﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Linq;

namespace Sami_SMS_CRM_Business
{
    public class MtQueueRepository : Repository
    {
        public IList<mt_queue> GetAll(string status, int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = DB.mt_queues.Where(p => string.IsNullOrEmpty(status) ? true : (p.status == status))
                                    .OrderByDescending(p => p.date_time);
            totalRecord = retValue.Count();
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        //public IList<mt_queue> GetAll(string subscriber, int? advertisment_id, int? collection_id, string status, DateTime? timeFrom, DateTime? timeTo,
        //                                int currentPage, int pageSize, ref int totalRecord)
        //{
        //    var retValue = DB.mt_queues.Where(p =>
        //                                (string.IsNullOrEmpty(subscriber) ? true : (p.subs.Contains(subscriber))) &&
        //                                (advertisment_id == null ? true : (p.advertisment_id == advertisment_id)) &&
        //                                (collection_id == null ? true : (p.collection_id == collection_id)) &&
        //                                (string.IsNullOrEmpty(status) ? true : (p.status == status)) &&
        //                                (timeFrom == null ? true : (p.date_time >= timeFrom)) &&
        //                                (timeTo == null ? true : (p.date_time <= timeTo))
        //                             )
        //                            .OrderByDescending(p => p.date_time);
        //    totalRecord = retValue.Count();
        //    return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        //}

        public DataTable GetAll(string subscriber, int? advertisment_id, int? collection_id, string status, DateTime? timeFrom, DateTime? timeTo,
                                        int currentPage, int pageSize, ref int totalRecord)
        {
            DataTable retTable = new DataTable();
            StringBuilder whereClause = new StringBuilder("WHERE 1=1");
            if (!string.IsNullOrEmpty(subscriber))
                whereClause.AppendLine("AND mq.subs LIKE @subscriber");
            if (advertisment_id != null)
                whereClause.AppendLine("AND @advertisment_id = mq.advertisment_id");
            if (collection_id != null)
                whereClause.AppendLine("AND @collection_id = mq.collection_id");
            if (!string.IsNullOrEmpty(status))
                whereClause.AppendLine("AND @status = mq.[status]");
            if (timeFrom != null)
                whereClause.AppendLine("AND @timeFrom <= mq.date_time");
            if (timeTo != null)
                whereClause.AppendLine("AND mq.date_time <= @timeTo");
            string query = string.Format(
                "DECLARE @tmp TABLE (\n" +
                "    [No] INT,\n" +
                "    mt_queue_id UNIQUEIDENTIFIER PRIMARY KEY \n" +
                ")\n" +
                "INSERT INTO @tmp\n" +
                "SELECT ROW_NUMBER() OVER (ORDER BY date_time DESC), mq.mt_queue_id \n" +
                "FROM mt_queue mq\n" +
                "{0}\n" +
                "SELECT * FROM mt_queue mq INNER JOIN @tmp T ON mq.mt_queue_id = T.mt_queue_id \n" +
                "WHERE T.[No] BETWEEN {1} AND {2}\n" +
                "ORDER BY date_time DESC \n" +
                "SELECT @retValue = COUNT(*) FROM @tmp",
                whereClause, (currentPage - 1) * pageSize + 1, currentPage * pageSize);
            using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@subscriber", "%" + subscriber + "%" ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@advertisment_id", advertisment_id ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@collection_id", collection_id ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@status", status ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@timeFrom", timeFrom ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@timeTo", timeTo ?? (object)DBNull.Value);
                    cmd.Parameters.Add("@retValue", SqlDbType.Int).Direction = ParameterDirection.Output;
                    retTable.Load(cmd.ExecuteReader());
                    totalRecord = (int)cmd.Parameters["@retValue"].Value;
                }
            }
            return retTable;
        }
    }
}
